﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.Services.Description;
using Oracle.DataAccess.Client;
using System.Net.Mail;

namespace WebApplication
{
    public partial class Reserveren : System.Web.UI.Page
    {
        OracleConnection conn;
        OracleCommand command;
        OracleDataReader reader;

        protected void Page_Load(object sender, EventArgs e)
        {
            conn = new OracleConnection();
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnectionString"].ToString();
            ZoekAccount();
        }

        //check = 0: OnlyLetter, check = 1: OnlyNumbers, check = 2: Number And Letters
        private bool CheckString(String tekst, int check)
        {
            String text = tekst.Trim();

            if (check == 0)
            {
                for (int i = 0; i < text.Length; i++)
                {
                    if (!Char.IsLetter(Convert.ToChar(text.Substring(i, 1))))
                    {
                        return false;
                    }
                }
                return true;
            }
            else if (check == 1)
            {
                for (int i = 0; i < text.Length; i++)
                {
                    if (!Char.IsNumber(Convert.ToChar(text.Substring(i, 1))))
                    {
                        return false;
                    }
                }
                return true;
            }
            else if (check == 2)
            {
                for (int i = 0; i < text.Length; i++)
                {
                    if (!(Char.IsLetter(Convert.ToChar(text.Substring(i, 1))) || Char.IsNumber(Convert.ToChar(text.Substring(i, 1)))))
                    {
                        return false;
                    }
                }
                return true;
            }
            return true;
        }

        private bool CheckTextBoxes()
        {
            bool correct = true;

            if (!((tbNaam.Text.Trim().Length > 0) && CheckString(tbNaam.Text, 0)))
            {
                lblNaam.BgColor = "Red";
                correct = false;
            }
            else
            {
                lblNaam.BgColor = "Transparent";
            }
            if (!((tbAdres.Text.Trim().Length > 0) && CheckString(tbAdres.Text, 2)))
            {
                lblAdres.BgColor = "Red";
                correct = false;
            }
            else
            {
                lblAdres.BgColor = "Transparent";
            }
            if (!((tbPostcode.Text.Trim().Length > 0) && CheckString(tbPostcode.Text, 2)))
            {
                lblPostcode.BgColor = "Red";
                correct = false;
            }
            else
            {
                lblPostcode.BgColor = "Transparent";
            }
            if (!((tbWoonplaats.Text.Trim().Length > 0) && CheckString(tbWoonplaats.Text, 0)))
            {
                lblWoonplaats.BgColor = "Red";
                correct = false;
            }
            else
            {
                lblWoonplaats.BgColor = "Transparent";
            }
            if (!((tbTelefoonnummer.Text.Trim().Length > 0) && CheckString(tbTelefoonnummer.Text, 1)))
            {
                lblTelnummer.BgColor = "Red";
                correct = false;
            }
            else
            {
                lblTelnummer.BgColor = "Transparent";
            }
            if (!((tbEmail.Text.Trim().Length > 0)))
            {
                lblEmail.BgColor = "Red";
                correct = false;
            }
            else
            {
                lblEmail.BgColor = "Transparent";
            }
            if (!((tbRekeningnummer.Text.Trim().Length > 0) && CheckString(tbRekeningnummer.Text, 1)))
            {
                lblRekeningnummer.BgColor = "Red";
                correct = false;
            }
            else
            {
                lblRekeningnummer.BgColor = "Transparent";
            }

            if (correct == false)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        
        private void ZoekAccount()
        {
            List<String> zoekaccount = new List<String>();
            string naam = tbEmail.Text;
            string sql;
            OracleCommand cmd;
            OracleDataReader reader;
            sql = "SELECT Naam FROM Klant_Betalend WHERE rfid = '" + User.Identity.Name + "'";
            cmd = new OracleCommand(sql, conn);
            conn.Open();
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                zoekaccount.Add(reader[0].ToString());
            }
            sql = "SELECT straat FROM Klant_Betalend WHERE rfid = '" + User.Identity.Name + "'";
            cmd = new OracleCommand(sql, conn);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                zoekaccount.Add(reader[0].ToString());
            }
            sql = "SELECT postcode FROM Klant_Betalend WHERE rfid = '" + User.Identity.Name + "'";
            cmd = new OracleCommand(sql, conn);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                zoekaccount.Add(reader[0].ToString());
            }
            sql = "SELECT woonplaats FROM Klant_Betalend WHERE rfid = '" + User.Identity.Name + "'";
            cmd = new OracleCommand(sql, conn);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                zoekaccount.Add(reader[0].ToString());
            }
            sql = "SELECT telefoon FROM Klant_Betalend WHERE rfid = '" + User.Identity.Name + "'";
            cmd = new OracleCommand(sql, conn);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                zoekaccount.Add(reader[0].ToString());
            }
            sql = "SELECT email FROM Klant_Betalend WHERE rfid = '" + User.Identity.Name + "'";
            cmd = new OracleCommand(sql, conn);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                zoekaccount.Add(reader[0].ToString());
            }
            sql = "SELECT rekeningnummer FROM Klant_Betalend WHERE rfid = '" + User.Identity.Name + "'";
            cmd = new OracleCommand(sql, conn);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                zoekaccount.Add(reader[0].ToString());
            }
            sql = "SELECT sofinummer FROM Klant_Betalend WHERE rfid = '" + User.Identity.Name + "'";
            cmd = new OracleCommand(sql, conn);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                zoekaccount.Add(reader[0].ToString());
            }
            conn.Close();

            tbNaam.Text = zoekaccount[0];
            tbAdres.Text = zoekaccount[1];
            tbPostcode.Text = zoekaccount[2];
            tbWoonplaats.Text = zoekaccount[3];
            tbTelefoonnummer.Text = zoekaccount[4];
            tbEmail.Text = zoekaccount[5];
            tbRekeningnummer.Text = zoekaccount[6];
            tbSofinummer.Text = zoekaccount[7];
            
        }
        public void sendmail(string emailid)
        {

            string smsg = "Bedankt voor het reserveren op onze site. <br>";
            smsg += "<br>Met vriendelijke groeten,<br><br><br>";
            smsg += "Management, Camping De Valkenhof";
            try
            {
                MailMessage message = new MailMessage("Camping.DeValkenhof@gmail.com", emailid);
                message.Subject = "Bevestiging reservering Camping De Valkenhof.";
                message.Body = smsg;
                message.IsBodyHtml = true;
                SmtpClient client = new SmtpClient();
                client.Port = 587; // Gmail works on this port 587, anderen 25
                client.Host = "smtp.gmail.com";
                System.Net.NetworkCredential nc = new System.Net.NetworkCredential("Camping.DeValkenhof@gmail.com", "onraadbaar");
                client.EnableSsl = true;
                client.UseDefaultCredentials = false;
                client.Credentials = nc;
                client.Send(message);
            }
            catch
            {
                //catch block goes here
            }
        }

        protected void SubmitButtonClick(object sender, EventArgs e)
        {
            string sql;
            List<String> listplaatsen = new List<String>();
            OracleCommand cmd;
            OracleDataReader reader;
            string s = "";
            bool bezet = false;
            conn.Open();

            sql = "SELECT Plaatsnummer FROM Reservering_plaats";
            cmd = new OracleCommand(sql, conn);
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                listplaatsen.Add(reader[0].ToString());
            }

            for (int i = 0; i < listplaatsen.Count; i++)
            {
                if(cbKampeerplaatsen.SelectedValue == listplaatsen[i]){
                    bezet = true;
                }
            }

            if (bezet)
            {
                Response.Write("<script>alert('Kampeerplaats is al bezet.');</script>");
            }
            else {
                sql = "SELECT MAX(reserveringsnummer) FROM reservering";
                cmd = new OracleCommand(sql, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    s = reader[0].ToString();
                }

                Convert.ToInt32(s);
                s = s + 1;
                Convert.ToString(s);
                sql = "INSERT INTO reservering VALUES ('" + s + "','false')";
                cmd = new OracleCommand(sql, conn);
                cmd.ExecuteNonQuery();
                sql = "UPDATE klant_betalend SET rfid = '" + User.Identity.Name + "' , naam = '" + tbNaam.Text + "' , straat = '" + tbAdres.Text + "' , postcode = '" + tbPostcode.Text + "' , woonplaats = '" + tbWoonplaats.Text + "' , telefoon = '" + tbTelefoonnummer.Text + "' , email = '" + tbEmail.Text + "' , rekeningnummer = '" + tbRekeningnummer.Text + "' , sofinummer = '" + tbSofinummer.Text + "' , reserveringsnummer = '" + s + "' , geactiveerd = 'YES' WHERE rfid = '" + User.Identity.Name + "'";
                cmd = new OracleCommand(sql, conn);
                cmd.ExecuteNonQuery();
                sql = "INSERT INTO reservering_plaats VALUES ('" + s + "','" + cbKampeerplaatsen.SelectedValue + "')";
                cmd = new OracleCommand(sql, conn);
                cmd.ExecuteNonQuery();

                int aantalpers = Convert.ToInt32(tbAantalPersonen.Text);

                if (aantalpers > 1)
                {
                    for (int i = 0; i < aantalpers - 1; i++)
                    {
                        string rfid = GetNewRFID();
                        string wachtwoord = "";

                        sql = "SELECT WACHTWOORD FROM PERSOON WHERE RFID = '" + User.Identity.Name + "'";
                        cmd = new OracleCommand(sql, conn);
                        reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            wachtwoord = reader[0].ToString();
                        }

                        sql = "INSERT INTO Persoon VALUES ('" + rfid + "', '" + wachtwoord + "', 'Klant')";
                        cmd = new OracleCommand(sql, conn);
                        cmd.ExecuteNonQuery();
                        sql = "INSERT INTO KLANT VALUES ('" + rfid + "', '" + s + "')";
                        cmd = new OracleCommand(sql, conn);
                        cmd.ExecuteNonQuery();
                    }
                }

                conn.Close();
                sendmail(tbEmail.Text);
            }
        }

        private string GetNewRFID()
        {
            List<String> rfidList = new List<String>();
            string sql = "SELECT RFID FROM RFID_COL WHERE RFID NOT IN(SELECT RFID FROM PERSOON) AND RFID NOT IN (SELECT RFID FROM KLANT)";
            command = new OracleCommand(sql, conn);

            reader = command.ExecuteReader();

            while (reader.Read())
            {
                rfidList.Add(reader[0].ToString());
            }


            return rfidList[0];
        }

        protected void ResetButtonClick(object sender, EventArgs e) { 
        
        }

    }
}







